﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetClassPath]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetClassPath];
GO
CREATE PROCEDURE [dbo].[sproc_GetClassPath]
    @ClassID INT,
    @Top INT = 0
/*
=========================================================
功能:    得到到此类的路径(从此类开始到根节点的路径
参数:
    @ClassID INT        :    接点的ID
    @Top INT = 0        :    取路径的前几步
备注:    待优化
=========================================================
*/
AS

DECLARE @id INT
DECLARE @pid INT

SET NoCount ON

CREATE TABLE #path (ID INT identity(1,1),classid INT,classname nvarchar(255),classremark ntext)    

--备份原ID
SET @ID = @ClassID
SET @pid =0

SELECT @pid=classparentid 
    FROM 
        uds_class 
    WHERE classid=@id

INSERT INTO #path 
        SELECT classid,classname,classremark 
            FROM 
                uds_class 
            WHERE classid = @id

WHILE @pid<>@id
BEGIN    
    SET @id = @pid
    INSERT INTO #path 
        SELECT classid,classname,classremark 
            FROM 
                uds_class 
            WHERE classid = @id
    SELECT @pid=classparentid 
        FROM 
            uds_class 
        WHERE classid=@id
    
End

IF @TOP = 0
    EXEC ('SELECT classid,classname,classremark FROM #path') --order by id desc
ELSE
    EXEC ('SELECT top ' + @top + 'classid,classname,classremark FROM #path') --order by id desc

DROP TABLE #path

SET NoCount OFF